package cn.edu.seig.textbook.mapper;

import cn.edu.seig.textbook.domain.pojo.Purchases;
import cn.edu.seig.textbook.domain.pojo.Textbooks;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author day
 * @since 2024-11-07
 */
@Mapper
public interface PurchasesMapper extends BaseMapper<Purchases> {

    // 根据学生姓名查询订单中教材的ID，再根据教材ID查询教材的所有信息
    @Select("SELECT t.* " +
            "FROM tb_orders o " +
            "JOIN tb_students s ON o.student_id = s.student_id " +
            "JOIN tb_textbooks t ON o.textbook_id = t.textbook_id " +
            "WHERE s.name LIKE CONCAT('%', #{studentName}, '%')")
    List<Textbooks> getTextbooksByStudentName(@Param("studentName") String studentName);

    // 获取订购教材的数量：统计订单中同一名学生如果出现同一教材ID，则数量+1，默认为1
    @Select("SELECT p.textbook_id, SUM(p.purchase_quantity) AS total_quantity " +
            "FROM tb_purchases p " +
            "JOIN tb_orders o ON p.textbook_id = o.textbook_id " +
            "JOIN tb_students s ON o.student_id = s.student_id " +
            "WHERE s.name LIKE CONCAT('%', #{studentName}, '%') " +
            "GROUP BY p.textbook_id")
    @Results({
            @Result(property = "textbookId", column = "textbook_id"),
            @Result(property = "purchaseQuantity", column = "total_quantity")  // 映射到 totalQuantity 属性
    })
    List<Purchases> getPurchaseQuantityByStudentName(@Param("studentName") String studentName);

    // 获取教材的总价：根据各个教材的单价 * 数量加起来计算总价
    @Select("SELECT SUM(t.price * p.purchase_quantity) AS total_price " +
            "FROM tb_purchases p " +
            "JOIN tb_textbooks t ON p.textbook_id = t.textbook_id " +
            "JOIN tb_orders o ON o.textbook_id = p.textbook_id " +
            "JOIN tb_students s ON o.student_id = s.student_id " +
            "WHERE s.name LIKE CONCAT('%', #{studentName}, '%')")
    Double getTotalPriceByStudentName(@Param("studentName") String studentName);

}
